<%@ page import="java.sql.*" %>
<%@ include file="billingmethodsp2.jsp" %>


<%!
    String dconv(String str) {
        String[] stra = str.split("/");
        // 11/12/2007
        String strb = stra[2] + '-' + stra[0] + '-' + stra[1];
        // 2007-11-12
        return strb;
    }
%>

<%

String connectionURL = "jdbc:mysql://localhost/crm2?user=root;password=root";
Connection connection = null;
Statement statement = null;
ResultSet rs, rsc = null;
String flag, pagenumber = null;
String where = "";
double totalentries = 0;
double totalpages = 0;
double entriesperpage = 3;
int prevpage;
int nextpage;
int offset = 0;


Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "root");
//statement = connection.createStatement();



flag = request.getParameter("flag");
if(flag==null) flag = "";

if (flag.equals("Search")) {
    out.println("<br><br><br>Search");
    String datesentfrom = dconv(request.getParameter("datesentfrom"));
    String datesentfromt = datesentfrom + " 00:00:01";
    String datesentto = dconv(request.getParameter("datesentto"));
    String datesenttot = datesentto + " 24:00:00";    
    out.println(datesentfromt);
    out.println(datesenttot);
    where = " WHERE " +
            "aDateSent >= '" + datesentfromt + "' " +
            " AND aDateSent <= '" + datesenttot + "' ";
    
}


String sql = "SELECT count(*) as count FROM billingcollection " + where + "";
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rsc = statement.executeQuery(sql);
if( rsc.absolute(1) ){
        totalentries= rsc.getInt("count");
}

pagenumber = request.getParameter("pagenumber");
if(pagenumber==null) pagenumber = "1";
entriesperpage = 3;
totalpages = Math.round(totalentries / entriesperpage);
//out.println("<br><br><br><br><br><br>"+ (int)totalpages + ' ' + totalentries + ' ' + entriesperpage );
int ipagenumber = Integer.parseInt(pagenumber);
offset = (ipagenumber - 1) * (int)entriesperpage;

prevpage = 1;
if(ipagenumber>1) { prevpage = ipagenumber - 1; }
nextpage = (int)totalpages;
if(ipagenumber < totalpages) { nextpage = ipagenumber + 1; }


if (flag.equals("Search")) {
    out.println("Search");
}


rs = statement.executeQuery("SELECT *, " +
        "DATE_FORMAT(aDateSent,'%m/%d/%Y %h:%i %p') as paDateSent, " +
        "DATE_FORMAT(aDateConfirm,'%m/%d/%Y %h:%i %p') as paDateConfirm " +
        "FROM billingcollection " + where + "" +
        "order by aDateSent DESC limit "+ offset + ", " + (int)entriesperpage + "");

String navigation = "" +
        "<a href='billing.jsp?pagenumber=1&datesentfrom=11/23/2007&datesentto=11/23/2007&salesref=ALL&template=ALL&datesentcheck='>First Page</a> " +
        "| <a href='billing.jsp?pagenumber=" + (int)prevpage + "&datesentfrom=11/23/2007&datesentto=11/23/2007&salesref=ALL&template=ALL&datesentcheck='>Prev Page</a> " + 
        "| <a href='billing.jsp?pagenumber=" + (int)nextpage + "&datesentfrom=11/23/2007&datesentto=11/23/2007&salesref=ALL&template=ALL&datesentcheck='>Next Page</a> " + 
        "| <a href='billing.jsp?pagenumber=" + (int)totalpages + "&datesentfrom=11/23/2007&datesentto=11/23/2007&salesref=ALL&template=ALL&datesentcheck='>Last Page</a> " +
        "<br>Showing 1 to 18 of " + (int)totalentries + " | Page " + ipagenumber + "/" + (int)totalpages + " ";
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>E-Z Movers Control Panel</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="./css/demo.css" />
<link rel="stylesheet" type="text/css" href="./css/office_xp/office_xp.css" />
<script type="text/javascript" src="./js/jsdomenu.js"></script>
<script type="text/javascript" src="./js/jsdomenubar.js"></script>
<script type="text/javascript" src="./js/ez.inc.js"></script>
	<SCRIPT LANGUAGE="JavaScript" SRC="CalendarPopup.js"></SCRIPT>

	<SCRIPT LANGUAGE="JavaScript">
	var cal = new CalendarPopup();
	</SCRIPT>

	<script language="javascript" type="text/javascript">
	<!--
	function popitup(url) {
		newwindow=window.open(url,'name','height=400,width=300, resizable, scrollbars');
		if (window.focus) {newwindow.focus()}
		return false;
	}
	
	// -->
	</script>

</head>
<body onload="initjsDOMenu()">
	<SCRIPT LANGUAGE="JavaScript" SRC="./js/CalendarPopup.js"></SCRIPT>
	<SCRIPT LANGUAGE="JavaScript">
	var cal = new CalendarPopup();
	</SCRIPT>

	<script language="javascript" type="text/javascript">
	<!--
	function popitup(url) {
		newwindow=window.open(url,'name','height=400,width=300, resizable, scrollbars');
		if (window.focus) {newwindow.focus()}
		return false;
	}
	
	// -->
	</script>
	


<center><br><br><h2><a href="billing.jsp">E-Z Movers Billing Collection Report</a></h2><br><br></center>
	<form NAME="example" method="GET" action="billing.jsp"><center>

	&nbsp;Date Sent Range&nbsp;&nbsp;<INPUT TYPE="text" NAME="datesentfrom" VALUE="11/23/2007" SIZE=25>
<A HREF="#"
   onClick="cal.select(document.forms['example'].datesentfrom,'anchor1','MM/dd/yyyy'); return false;"
   NAME="anchor1" ID="anchor1">select</A>
&nbsp;&nbsp;&nbsp;
<INPUT TYPE="text" NAME="datesentto" VALUE="11/23/2007" SIZE=25>
<A HREF="#"
   onClick="cal.select(document.forms['example'].datesentto,'anchor2','MM/dd/yyyy'); return false;"
   NAME="anchor2" ID="anchor2">select</A>

   

	<br><br>&nbsp;&nbsp;<input type="submit" name="flag" value="Search"><br><br></center></form>
        
        <center><br><br>
        <%= navigation %>
        <br><br>
     
<table width="80%" cellspacing=2 cellpadding=4>
<tr>
<td><b>No.</b></td>
<td><b>Job Number</b></td>
<td><b>DateSent</b></td>
<td><b>EmailOpened</b></td>
<td><b>DocumentSigned</b></td>
<td><b>CustomerName</b></td>
<td><b>CustomerEmail</b></td>
<td><b>SalesRep</b></td>
</tr>
        

<%  
    int x = offset;
    String bgcolor = "#ffffff";
    String pDateC = null;
    while (rs.next()) { 
        x = x + 1;
        int k = x % 2;
        if(k==0) { bgcolor = "#d3d3d3"; }
        if(k==1) { bgcolor = "#ffffff"; }
        pDateC = rs.getString("paDateConfirm");
        if (pDateC==null) { pDateC=""; }
        
%>
<tr bgcolor='<%= bgcolor %>'><td><%= x %></td>
<td nowrap='nowrap'><%= rs.getString("aJobNum") %></td>
<td nowrap='nowrap'><%= rs.getString("paDateSent") %></td>
<td nowrap='nowrap'><%= pDateC %></td>
<td nowrap='nowrap'><%= pDateC %></td>
<td nowrap='nowrap'><%= rs.getString("aCustomer") %></td>
<td nowrap='nowrap'><%= rs.getString("aCustomerEmail") %></td>
<td nowrap='nowrap'><%= rs.getString("aSalesRep") %></td>
</tr>

<% } rs.close(); %>

<table>
                
                
<br><br><%= navigation %></center>

</body>
</html>